package edu.uoc.tdp.pac3.server;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.sql.Statement;
import javax.swing.JOptionPane;

import edu.uoc.tdp.pac3.beans.Actuacio;
import edu.uoc.tdp.pac3.common.tools.TDSLanguageUtils;

/**
 * Classe per gestionar l'acces a la Taula d'anotacions
 * 
 * @author Eloi Rebollo i Francesc Cabrera
 * @version 1.0
 */
public class GestorActuacions {

    private Connection objConn;
    private TDSLanguageUtils mess = null;

    public GestorActuacions(GestorDisc gestor) {
        objConn = gestor.getConnection();
    }

    public Connection getConnection() {
        return objConn;
    }

    /**
     * Recerca una anotacio segons l'identificador
     * @param Identificador de la notificacio
     * @return L'objecte de la classe Anotacio amb identificador igual que el parametre
     */
    public Actuacio getNote(int idNote) {
        Actuacio note = null;

        String consulta = "SELECT check_id, request_id, user_id creation_date, description"
                + "FROM tb_request_note WHERE check_id =  " + idNote;
        try {
            Statement query = getConnection().createStatement();
            ResultSet rs = query.executeQuery(consulta);
            if (rs.next()) {
                note = new Actuacio(rs.getInt(1), rs.getInt(2),rs.getDate(4), rs.getString(5));
            }
            rs.close();
            query.close();

        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null,
                    mess.getMessage("gestor.note.err.db") + "\n" + e.getMessage(),
                    mess.getMessage("gestor.note.err"), JOptionPane.ERROR_MESSAGE);
        } catch (Exception eAl) {
            JOptionPane.showMessageDialog(null,
                    mess.getMessage("gestor.note.err.unknown") + "\n" + eAl.getMessage(),
                    mess.getMessage("gestor.note.err"), JOptionPane.ERROR_MESSAGE);
        }
        return note;
    }

    /**
     * Llista de totes les actuacions d'una incidència
     * @return Lista en totes les incidencies
     */
    public List<Actuacio> getNoteList(Integer idRequest) {

        List<Actuacio> noteList = new ArrayList<Actuacio>();
        try {
            String consulta = "SELECT * FROM tb_request_note WHERE request_id =  " + idRequest;

            Statement query = getConnection().createStatement();
            ResultSet rs = query.executeQuery(consulta);
            while (rs.next()) {
                //Integer idNote = new Integer(rs.getInt("check_id"));
                noteList.add(new Actuacio(rs.getInt(1), rs.getInt(2), rs.getInt(3),
                                          rs.getDate(4), rs.getString(5)));
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null,
                    mess.getMessage("gestor.note.err.db") + "\n" + e.getMessage(),
                    mess.getMessage("gestor.note.err"), JOptionPane.ERROR_MESSAGE);
        } catch (Exception eAl) {
            JOptionPane.showMessageDialog(null,
                    mess.getMessage("gestor.note.err.unknown") + "\n" + eAl.getMessage(),
                    mess.getMessage("gestor.note.err"), JOptionPane.ERROR_MESSAGE);
        }
        return noteList;
    }

    /**
     * Dona d'alta una actuacio nova a la DB
     * @return 
     */   
    public Integer addNote (Actuacio note) {
        Integer ret = 1;

        try {
            java.util.Date fecha = new java.util.Date();
            java.sql.Date SQLFecha = new java.sql.Date(fecha.getTime());
            
            String consulta = "INSERT INTO tb_request_note (request_id, user_id, creation_date, "
                    + "description) "
                    + " VALUES (?,?,?,?)";
            
            PreparedStatement paramQuery = getConnection().prepareStatement(consulta, PreparedStatement.RETURN_GENERATED_KEYS);            
            paramQuery.setInt(1, note.getIdRequest());
            paramQuery.setInt(2, note.getIdUser());
            paramQuery.setDate(3, SQLFecha);
            paramQuery.setString(4, note.getDescription());
            
            paramQuery.execute();

            // recuperem l'index del nou registre creat
            ResultSet rs = paramQuery.getGeneratedKeys();
            while (rs.next()) {
                ret = rs.getInt(1);                
            }
        } catch (SQLException e) {
            ret = -1;
            if (e.getSQLState().equals(GestorDisc.SQL_STATE_UNIQUE_VIOLATION)) {
                JOptionPane.showMessageDialog(null,
                        mess.getMessage("gestor.note.err.new") + "\n" + e.getMessage(),
                        mess.getMessage("gestor.note.err"), JOptionPane.ERROR_MESSAGE);
            }
        }
        return ret;
    }
}
